package com.bdqn.t350.dao.impl;

import com.bdqn.t350.dao.BaseDao;
import com.bdqn.t350.dao.PetDao;
import com.bdqn.t350.entity.Page;
import com.bdqn.t350.entity.Pet;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

public class PetDaoImp extends BaseDao implements PetDao {

    public static void main(String[] args) {
        PetDaoImp petDao = new PetDaoImp();
        Pet pet = new Pet();
        pet.setPetName("中");
        pet.setPetSex("雄");
        List<Pet> list = petDao.getList(pet);
        System.out.println(list.size());
    }


    Connection connection = null;
    PreparedStatement preparedStatement = null;
    ResultSet rs = null;


    public List<Pet> getList(Pet petEntity) {

        StringBuffer sql = new StringBuffer("SELECT `petId`,`petName`,`petBreed`,`petSex`, `birthday`,`desc` FROM `pet` where 1=1 ");
        Map<String, Object> map = new HashMap<>();
        if (petEntity.getPetName() != null) {
            map.put("petName", "%" + petEntity.getPetName().trim() + "%");
            sql.append(" and petName like ?");
        }

        if (petEntity.getPetSex() != null) {
            map.put("petSex", petEntity.getPetSex().trim());
            sql.append(" and petSex = ?");
        }
        sql.append(" order by petId");
        connection = this.getConnection();
        List<Pet> list = new ArrayList<>();
        Pet pet = null;
        try {
            preparedStatement = connection.prepareStatement(sql.toString());
            int i = 0;
            for (Map.Entry<String, Object> pet1 : map.entrySet()) {
                i++;
                preparedStatement.setObject(i, pet1.getValue());
            }
            rs = preparedStatement.executeQuery();
            while (rs.next()) {
                pet = new Pet(rs.getInt("petId"),
                        rs.getString("petName"),
                        rs.getString("petBreed"),
                        rs.getString("petSex"),
                        rs.getDate("birthday"),
                        rs.getString("desc"));
                list.add(pet);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            this.closeAll(connection, preparedStatement, rs);
        }
        return list;
    }

    @Override
    public List<Pet> findByPetBreed(String petBreed) {
        return null;
    }

    @Override
    public int count(String petName) {
        return 0;
    }

    @Override
    public int addPet(Pet pet) {
        return 0;
    }

    @Override
    public int tableCount() {
        String sql = "SELECT count(1) FROM `pet`";
        connection = this.getConnection();
        int count = 0;
        try {
            preparedStatement = connection.prepareStatement(sql);
            rs = preparedStatement.executeQuery();
            while (rs.next()) {
                count = rs.getInt(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //关闭链接
            this.closeAll(connection, preparedStatement, rs);
        }
        return count;
    }


    @Override
    public List<Pet> getPageList(Page page) {
        String sql = "SELECT `petId`,`petName`,`petBreed`,`petSex`, `birthday`,`desc` FROM `pet` order by petId limit ?,?";
        connection = this.getConnection();
        List<Pet> list = new ArrayList<>();
        Pet pet = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            //（当前要显示的页-1）*每页显示的记录数
            preparedStatement.setObject(1, (page.getCurrPageNo() - 1) * page.getPageSize());
            preparedStatement.setObject(2, page.getPageSize());
            rs = preparedStatement.executeQuery();
            while (rs.next()) {
                pet = new Pet(rs.getInt("petId"),
                        rs.getString("petName"),
                        rs.getString("petBreed"),
                        rs.getString("petSex"),
                        rs.getDate("birthday"),
                        rs.getString("desc"));
                list.add(pet);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //关闭链接
            this.closeAll(connection, preparedStatement, rs);
        }
        return list;
    }

    @Override
    public int updateBySex(Pet pet) {
        String sql = "UPDATE `pet` SET `petSex`=? WHERE (`petId`=?)";
        connection = this.getConnection();
        int count = 0;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setObject(1,pet.getPetSex());
            preparedStatement.setObject(2,pet.getPetId());
            count = preparedStatement.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            this.closeAll(connection, preparedStatement, rs);
        }
        return count;
    }
}
